/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fh.controller;

import fh.component.DataSource;
import fh.model.Player;
import java.sql.*;
import javax.swing.DefaultComboBoxModel;

/**
 *
 * @author Bart
 */
public class PlayerController {

    public boolean insertPlayer(Player p) {
        String insertPlayerQuery = "INSERT INTO `player` (firstname, lastname, address, zipcode, city, phonenumber, email, rating, money_won, vip) values('"
                + p.getFirstname() + "','"
                + p.getLastname() + "', '"
                + p.getAddress() + "', '"
                + p.getZipcode() + "', '"
                + p.getCity() + "', '"
                + p.getPhonenumber() + "', '"
                + p.getEmail() + "', '"
                + p.getRating() + "', '"
                + p.getMoney_won() + "', "
                + p.isVip() + ")";
        DataSource.executeUpdateQuery(insertPlayerQuery);
        return true;
    }

    public boolean updatePlayer(Player p) {
        String insertPlayerQuery = "UPDATE `player` SET "
                + "firstname = '" + p.getFirstname() + "', "
                + "lastname = '" + p.getLastname() + "', "
                + "address = '" + p.getAddress() + "', "
                + "zipcode ='" + p.getZipcode() + "', "
                + "city ='" + p.getCity() + "', "
                + "phonenumber = '" + p.getPhonenumber() + "', "
                + "email ='" + p.getEmail() + "', "
                + "rating =" + p.getRating() + ", "
                + "money_won =" + p.getMoney_won() + ", "
                + "vip =" + p.isVip()
                + " WHERE id = " + p.getId();
        DataSource.executeUpdateQuery(insertPlayerQuery);
        return true;
    }

    public DefaultComboBoxModel getPlayerList() {
        try {
            DefaultComboBoxModel players = new DefaultComboBoxModel();

            String query = "SELECT * FROM player ORDER BY firstname";

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Player p;
                while (rs.next()) {
                    p = new Player();
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));

                    players.addElement(p);
                }

                return players;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public Player getPlayerById(int id) {
        try {
            Player p = new Player();

            String query = "SELECT * FROM player WHERE id=?";
            System.out.println(query);
            ResultSet rs = DataSource.executeSelectWithIdQuery(query, id);
            System.out.println();
            if (rs != null) {
                while (rs.next()) {
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));
                    return p;
                }
                return p;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public int deletePlayerById(int id) {

        String query = "DELETE FROM player WHERE id=?";
        System.out.println(query);
        return DataSource.executeDeleteQuery(query, id);
    }

    public DefaultComboBoxModel getVIPPlayerList() {
        try {
            DefaultComboBoxModel players = new DefaultComboBoxModel();

            String query = "SELECT * FROM player WHERE vip = '1'";

            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Player p;
                while (rs.next()) {
                    p = new Player();
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));

                    players.addElement(p);
                }

                return players;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public DefaultComboBoxModel getMasterclassRegistrationList(int min, int max) {
        try {
            DefaultComboBoxModel players = new DefaultComboBoxModel();

            String query = "SELECT * FROM player WHERE rating BETWEEN "
                    + min
                    + " AND "
                    + max;
            System.out.println(query);
            ResultSet rs = DataSource.executeSelectQuery(query);
            if (rs != null) {
                Player p;
                while (rs.next()) {
                    p = new Player();
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));

                    players.addElement(p);
                }

                return players;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }

    public boolean insertRegistration(int p_id, int e_id) {
        String insertRegistration = "INSERT INTO player_event (player_id, event_id, payed) values("
                + p_id + ","
                + e_id + ","
                + false + ")";
        System.out.println(insertRegistration);
        DataSource.executeUpdateQuery(insertRegistration);
        return true;
    }

    public DefaultComboBoxModel getEventPlayerList(int id) {
        try {
            DefaultComboBoxModel players = new DefaultComboBoxModel();

            String query = "SELECT * FROM player P INNER JOIN player_event PE ON  P.id=PE.player_id WHERE event_id='" + id + "'";
            System.out.println(query);
            ResultSet rs = DataSource.executeSelectQuery(query);

            if (rs != null) {
                Player p;
                while (rs.next()) {
                    p = new Player();
                    p.setId(rs.getInt(1));
                    p.setFirstname(rs.getString(2));
                    p.setLastname(rs.getString(3));
                    p.setAddress(rs.getString(4));
                    p.setZipcode(rs.getString(5));
                    p.setCity(rs.getString(6));
                    p.setPhonenumber(rs.getString(7));
                    p.setEmail(rs.getString(8));
                    p.setRating(rs.getInt(9));
                    p.setMoney_won(rs.getInt(10));
                    p.setVip(rs.getBoolean(11));
                    players.addElement(p);
                }

                return players;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Deze error:");
            System.out.println(e);
            return null;
        }
    }
}
